<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of Tag
 *
 * @author QuangKhoi
 */
class Tag {

    //put your code here
    public $tagId;
    public $tagName;
    public $groupId;
    public $keywords;
    public $userId;
    public $editable;

    public function __constructor() {
        
    }

    public static function getList() {
        $db = Database::getInstance();
        $user = User::getLogedInUser();
        $query = "SELECT * FROM tags WHERE userid IN (SELECT userid FROM users WHERE teamid = $user->teamId) OR userid = '0'";
        if ($user->type == 10)
            $query = "SELECT * FROM tags WHERE userid = 33";
        $result = $db->query($query);
        if ($result->returnCode == 200) {
            if ($result->dataTable->numOfRow > 0) {
                $tags = array();
                foreach ($result->dataTable->rows as $row) {
                    $tag = new self();
                    $tag->tagId = $row["tagid"];
                    $tag->tagName = $row["tagname"];
                    $tag->groupId = $row["groupid"];
                    $tag->keywords = $row["keywords"];
                    $tag->userId = $row["userid"];
                    $tag->editable = false;
                    if ($user->managerId == 0)
                        $tag->editable = true;
                    $tags[] = $tag;
                }
                return $tags;
            } else {
                return NULL;
            }
        } else {
            return NULL;
        }
    }

    public static function withId($tagId) {
        $instance = new self();
        $user = User::getLogedInUser();
        $db = Database::getInstance();
        $result = $db->query("SELECT * FROM tags WHERE tagid = '$tagId'");
        if ($result->returnCode == 200) {
            if ($result->dataTable->numOfRow == 1) {
                $row = $result->dataTable->rows[0];
                $instance->tagId = $row["tagid"];
                $instance->tagName = $row["tagname"];
                $instance->groupId = $row["groupid"];
                $instance->keywords = $row["keywords"];
                $instance->userId = $row["userid"];
                $instance->editable = false;
                if ($user->managerId == 0)
                    $instance->editable = true;
                return $instance;
            } else {
                return NULL;
            }
        } else {
            return NULL;
        }
    }

    public static function getTag($messageId, $streamId) {
        $db = Database::getInstance();
        $userId = User::getCurrentUserId();
        $user = User::withId($userId);
        $userSQL = "WHERE tagsdata.userid IN (SELECT userid FROM users WHERE teamid = $user->teamId) ";
        if ($user->type == '10')
            $userSQL = '';
        $query = "SELECT tags.tagid, tags.tagname, tags.userid, tags.groupid, tags.keywords "
                . "FROM tagsdata "
                . "JOIN signals ON tagsdata.signalid = signals.signalid "
                . "JOIN tags ON tags.tagid = tagsdata.tagid "
                . $userSQL
                . "AND signals.messageid='$messageId' AND signals.streamid=$streamId";
        $result = $db->query($query);
        if ($result->returnCode == 200) {
            if ($result->dataTable->numOfRow > 0) {
                $tags = array();
                foreach ($result->dataTable->rows as $row) {
                    $tag = new self();
                    $tag->tagId = $row["tagid"];
                    $tag->tagName = $row["tagname"];
                    $tag->groupId = $row["groupid"];
                    $tag->keywords = $row["keywords"];
                    $tag->userId = $row["userid"];
                    $tag->editable = false;
                    if ($user->managerId == 0)
                        $tag->editable = true;
                    $tags[] = $tag;
                }
                return $tags;
            } else {
                return array();
            }
        }
        return array();
    }

    public static function deleteTag($tagid) {
        $db = Database::getInstance();
        $query = "DELETE FROM tags WHERE tagid = '$tagid'";
        $result = $db->query($query);
        if ($result->returnCode == 200) {
            return "true";
        } else {
            return "false";
        }
    }

    public function add($signalId) {
        $userId = $_SESSION["userid"];
        $query = "SELECT * FROM tagsdata WHERE tagid = '$this->tagId' AND signalid='$signalId'";
        $result = Database::getInstance()->query($query);
        if ($result->returnCode == 200)  //OK
            if ($result->dataTable->numOfRow == 0) {
                Database::getInstance()->query("INSERT INTO tagsdata 
                            (userid, tagid, signalid) VALUES
                            ('$userId', '$this->tagId',
                            '$signalId')");
                return $this;
            }
        return NULL;
    }

    public function Save() {
        $query = "UPDATE tags SET tagname = '$this->tagName',"
                . " keywords = '$this->keywords',"
                . " groupid = '$this->groupId' WHERE tagid='$this->tagId'";
        $result = Database::getInstance()->query($query);
        if ($result->returnCode == 200)  //OK
            return true;
        else
            return false;
    }

    public function remove($signalId) {
        $query = "DELETE FROM tagsdata WHERE tagid = '$this->tagId' AND signalid='$signalId'";
        $result = Database::getInstance()->query($query);
        if ($result->returnCode == 200)
            return 'succeed';
        return NULL;
    }

    public static function loadTasksTag($taskId) {
        $db = Database::getInstance();
        $userId = $_SESSION["userid"];
        $user = User::withId($userId);
        $query = "SELECT tagid FROM tagsdata WHERE tagsdata.userid IN (SELECT userid FROM users WHERE teamid = $user->teamId) AND taskid='$taskId'";
        $result = $db->query($query);
        if ($result->returnCode == 200) {
            if ($result->dataTable->numOfRow > 0) {
                $tags = array();
                foreach ($result->dataTable->rows as $row) {
                    $tag = self::withId($row["tagid"]);
                    $tags[] = $tag;
                }
                return $tags;
            } else {
                return array();
            }
        }
        return array();
    }

    public static function getMetric($_filter) {
        $rows['tagname'] = array();
        $user = User::getLogedInUser();
        if ($user->type == 10)
            $user->teamId = 3;
        $streamId = base64_decode($_filter["streamId"]);
        $db = Database::getInstance();
        $sentimentFilter = 0;
        if (isset($_filter['sentiment']))
            $sentimentFilter = $_filter['sentiment'];
        $sentiment = '';
        if ($sentimentFilter)
            $sentiment = "AND sentimentdata.score = " . (2 - $sentimentFilter);
        $tag = '';
        if ($_filter['tags'] != array('0')) {
            $tags = implode(',', $_filter['tags']);
            $tag = "AND tags.tagid IN ($tags)";
        }
        $date = '';
        if ($_filter['dateFrom'] && $_filter['dateTo']) {
            $dateFrom = new DateTime($_filter['dateFrom']);
            $dateTo = new DateTime($_filter['dateTo']);
            $date = 'AND sentimentdata.datecreated >= \'' . $dateFrom->format('Y-m-d') . ' 00:00:00\' AND sentimentdata.datecreated <= \'' . $dateTo->format('Y-m-d') . ' 23:59:59\'';
        }
        $sourceSQL = '';
        if ($_filter['source'] != array('0')) {
            $source = $_filter['source'];
            $sources = implode('\',\'', $source);
            $sourceSQL = " AND sentimentdata.source IN ('$source')";
        }
        $typeSQL = '';
        if ($_filter['type'] != array('0')) {
            $type = $_filter['type'];
            $types = implode(',', $type);
            $typeSQL = " AND sentimentdata.type IN ($types)";
        }
        $tagsMetric = array();
        $query = "SELECT tags.tagid, tags.tagname, score, count(score) AS count, sentimentdata.streamid, taggroups.name FROM signals LEFT JOIN sentimentdata ON sentimentdata.messageid = signals.messageid AND sentimentdata.streamid = signals.streamid INNER JOIN tagsdata ON tagsdata.signalid = signals.signalid INNER JOIN tags ON tags.tagid = tagsdata.tagid INNER JOIN taggroups ON taggroups.id = tags.groupid WHERE sentimentdata.streamid = $streamId $sentiment $tag $date $sourceSQL $typeSQL AND tags.userid IN (SELECT userid FROM users WHERE teamid=$user->teamId) GROUP BY tags.tagid, score";
        //echo $query;
        $result = $db->query($query);
        if ($result->returnCode == 200) {
            if ($result->dataTable->numOfRow > 0) {
                $rows = array();
                //echo $query;
                foreach ($result->dataTable->rows as $row) {
                    if (!isset($tagsMetric[$row['name']]))
                        $tagsMetric[$row['name']] = array();
                    if (!isset($tagsMetric[$row['name']]['tagname']))
                        $tagsMetric[$row['name']]['tagname'] = array();
                    if (is_array($tagsMetric[$row['name']]['tagname'])) {
                        if (!in_array($row['tagname'], $tagsMetric[$row['name']]['tagname']))
                            $tagsMetric[$row['name']]['tagname'][] = $row['tagname'];
                    }
                    else {
                        $tagsMetric[$row['name']]['tagname'][] = $row['tagname'];
                    }
                    $tagsMetric[$row['name']][$row['tagname']][$row['score']] = $row['count'];
                    $tagsMetric[$row['name']][$row['tagname']]['id'] = $row['tagid'];
                    if (!isset($tagsMetric[$row['name']][$row['tagname']]['total']))
                        $tagsMetric[$row['name']][$row['tagname']]['total'] = 0;
                    $tagsMetric[$row['name']][$row['tagname']]['total'] += $row['count'];
                }
                //print_r($rows);
            }
        }
        return $tagsMetric;
    }

    public static function findMin($rows) {
        $min = 999999999;
        foreach ($rows as $key => $row) {
            if ($row['total'] < $min)
                $min = $row['total'];
        }
        if ($min == 999999999)
            return false;
        return $min;
    }

    public static function addGroup($groupName) {
        $db = Database::getInstance();
        $userId = User::getCurrentUserId();
        $query = "INSERT INTO taggroups (name, userid) VALUES ('$groupName', $userId)";
        $group = self::getGroups($groupName, $userId);
        if (count($group) == 0) {
            $result = $db->query($query);
            if ($result->returnCode == 200) {
                $group = self::getGroups($groupName, $userId);
                if (count($group) == 1) {
                    return json_encode(array(
                        'id' => $group[0]['id'],
                        'name' => $group[0]['name'],
                        'userid' => $group[0]['userid']
                    ));
                } else {
                    return 'duplicate';
                }
            } else
                return 'null';
        }
        else {
            return 'duplicate';
        }
    }

    public static function getGroups($name = '', $userId = '') {
        $db = Database::getInstance();
        $user = User::getLogedInUser();
        if ($user->type == '10')
            $user->teamId = 3;
        $query = "SELECT * FROM taggroups WHERE userid = 0 OR userid IN (SELECT userid FROM users WHERE teamid = $user->teamId)";
        if ($name != '' && $userId != '')
            $query = "SELECT * FROM taggroups WHERE userid = $userId AND name = '$name'";
        $result = $db->query($query);
        if ($result->returnCode == 200) {
            return $result->dataTable->rows;
        }
    }

    public static function getTagsByGroup($groupId = '0') {
        $db = Database::getInstance();
        $user = User::getLogedInUser();
        if ($user->type == '10')
            $user->teamId = 3;
        $query = "SELECT * FROM tags WHERE groupid = $groupId AND (userid = '0' OR userid IN (SELECT userid FROM users WHERE teamid = $user->teamId))";
        if ($groupId == '0')
            $query = "SELECT * FROM tags WHERE groupid IN (SELECT groupid FROM taggroups WHERE userid = 0 OR userid IN (SELECT userid FROM users WHERE teamid = $user->teamId)) AND (userid = '0' OR userid IN (SELECT userid FROM users WHERE teamid = $user->teamId))";
        $result = $db->query($query);
        if ($result->returnCode == 200) {
            if ($result->dataTable->numOfRow > 0) {
                $tags = array();
                foreach ($result->dataTable->rows as $row) {
                    $tags[] = self::withId($row['tagid']);
                }
                return $tags;
            }
            return null;
        }
        return null;
    }

    public static function listForDetection() {
        $db = Database::getInstance();
        $user = User::getLogedInUser();
        $result = $db->query("SELECT * FROM tags WHERE keywords != '' AND (userid IN (SELECT userid FROM users WHERE teamid = $user->teamId) OR userid = '0')");
        if ($result->returnCode == 200) {
            if ($result->dataTable->numOfRow > 0) {
                $tags = array();
                foreach ($result->dataTable->rows as $row) {
                    $tag = new self();
                    $tag->tagId = $row["tagid"];
                    $tag->tagName = $row["tagname"];
                    $tag->groupId = $row["groupid"];
                    $tag->keywords = $row["keywords"];
                    $tag->userId = $row["userid"];
                    $tag->editable = false;
                    if ($user->managerId == 0)
                        $tag->editable = true;
                    $tags[] = $tag;
                }
                return $tags;
            } else {
                return NULL;
            }
        } else {
            return NULL;
        }
    }

    public static function detectTag($message, $messageId, $streamId) {
        $result = array();
        $tags = self::listForDetection();
        if (is_array($tags))
            foreach ($tags as $tag) {
                $words = explode(', ', $tag->keywords);
                $contain = false;
                foreach ($words as $word) {
                    if (strpos($message, $word) !== false) {
                        $contain = true;
                        break;
                    }
                }
                if ($contain) {
                    bpServer::addTag($messageId, $tag->tagId, $streamId);
                    $result[] = $tag;
                }
            }
        return $result;
    }

    public static function getCompetitorsMetrics($_filter) {
        $rows['tagname'] = array();
        $user = User::getLogedInUser();
        $streamId = base64_decode($_filter["streamId"]);
        $db = Database::getInstance();
        $sentiment = '';
        $sentimentFilter = 0;
        if (isset($_filter['sentiment']))
            $sentimentFilter = $_filter['sentiment'];
        if ($sentimentFilter)
            $sentiment = "AND sentimentdata.score = " . (2 - $sentimentFilter);
        $tag = '';
        if ($_filter['tags'] != array('0')) {
            $tags = implode(',', $_filter['tags']);
            $tag = "AND tags.tagid IN ($tags)";
        }
        $date = '';
        if ($_filter['dateFrom'] && $_filter['dateTo']) {
            $dateFrom = new DateTime($_filter['dateFrom']);
            $dateTo = new DateTime($_filter['dateTo']);
            $date = 'AND sentimentdata.datecreated >= \'' . $dateFrom->format('Y-m-d') . ' 00:00:00\' AND sentimentdata.datecreated <= \'' . $dateTo->format('Y-m-d') . ' 23:59:59\'';
        }
        $sourceSQL = '';
        if ($_filter['source'] != array('0')) {
            $source = $_filter['source'];
            $sources = implode('\',\'', $source);
            $sourceSQL = " AND sentimentdata.source IN ('$sources')";
        }
        $typeSQL = '';
        if ($_filter['type'] != array('0')) {
            $type = $_filter['type'];
            $types = implode(',', $type);
            $typeSQL = " AND sentimentdata.type IN ($types)";
        }
        $tagsMetric = array();
        $query = "SELECT DISTINCT sentimentdata.messageid "
                . "FROM signals "
                . "INNER JOIN tagsdata ON tagsdata.signalid = signals.signalid "
                . "INNER JOIN tags ON tags.tagid = tagsdata.tagid "
                . "INNER JOIN taggroups ON tags.groupid = taggroups.id "
                . "INNER JOIN sentimentdata ON sentimentdata.messageid = signals.messageid "
                . "AND name = 'Competitors' AND sentimentdata.streamid = $streamId $sentiment $tag $date $sourceSQL $typeSQL AND sentimentdata.score!= 0 AND tags.userid IN (SELECT userid FROM users WHERE teamid=$user->teamId)";
        //echo $query;
        $result = $db->query($query);
        if ($result->returnCode == 200) {
            if ($result->dataTable->numOfRow > 0) {
                $messageIds = array();
                foreach ($result->dataTable->rows as $row) {
                    $messageIds[] = $row[0];
                }
                $messageIdQuery = implode(',', $messageIds);
                $query = "SELECT groupid, name as groupname, tagname, score, signals.messageid FROM `signals` LEFT JOIN tagsdata ON tagsdata.signalid = signals.signalid LEFT JOIN tags ON tagsdata.tagid = tags.tagid LEFT JOIN taggroups ON taggroups.id = tags.groupid LEFT JOIN sentimentdata ON sentimentdata.messageid = signals.messageid WHERE signals.messageid IN ($messageIdQuery) ORDER BY sentimentdata.messageid ASC, groupid DESC";
                //echo $query;
                $result = $db->query($query);
                if ($result->returnCode == 200) {
                    if ($result->dataTable->numOfRow > 0) {
                        $rows = array();
                        //echo $query;
                        $rows['tagname'] = array();
                        $curCompetitor = '';
                        foreach ($result->dataTable->rows as $row) {
                            /*  */

                            if ($row['groupname'] == 'Competitors') {
                                $curCompetitor = $row['tagname'];
                                $tagsMetric[$row['messageid']][$row['tagname']] = array();
                            } else {
                                if (!isset($tagsMetric[$row['messageid']][$curCompetitor]['tagname']))
                                    $tagsMetric[$row['messageid']][$curCompetitor]['tagname'] = array();
                                if (!isset($tagsMetric[$row['messageid']][$curCompetitor][$row['tagname']][$row['score']]))
                                    $tagsMetric[$row['messageid']][$curCompetitor][$row['tagname']][$row['score']] = 0;
                                $tagsMetric[$row['messageid']][$curCompetitor][$row['tagname']][$row['score']] += 1;
                                if (!in_array($row['tagname'], $tagsMetric[$row['messageid']][$curCompetitor]))
                                    $tagsMetric[$row['messageid']][$curCompetitor]['tagname'][] = $row['tagname'];
                            }
                        }
                        //re-processing
                        $result = array();
                        foreach ($tagsMetric as $message) {
                            foreach ($message as $competitorName => $competitorTags) {
                                foreach ($competitorTags as $key => $value) {
                                    if (!isset($result[$competitorName]))
                                        $result[$competitorName] = array();
                                    if (!isset($result[$competitorName][$key]))
                                        $result[$competitorName][$key] = array();
                                    if (!isset($result[$competitorName][$key][0]))
                                        $result[$competitorName][$key][0] = 0;
                                    if (!isset($result[$competitorName][$key][-1]))
                                        $result[$competitorName][$key][-1] = 0;
                                    if (!isset($result[$competitorName][$key][-3]))
                                        $result[$competitorName][$key][-3] = 0;
                                    if (!isset($result[$competitorName][$key][1]))
                                        $result[$competitorName][$key][1] = 0;
                                    if (!isset($result[$competitorName][$key]['total']))
                                        $result[$competitorName][$key]['total'] = 0;
                                    if (!isset($value[0])) $value[0] = 0;
                                    if (!isset($value[1])) $value[1] = 0;
                                    if (!isset($value[-1])) $value[-1] = 0;
                                    if (!isset($value[-3])) $value[-3] = 0;
                                    if (count($competitorTags))
                                        $result[$competitorName][$key][0] += $value[0];
                                    $result[$competitorName][$key][1] += $value[1];
                                    $result[$competitorName][$key][-1] += $value[-1];
                                    $result[$competitorName][$key][-3] += $value[-3];
                                    $result[$competitorName][$key]['total'] += $value[0] + $value[1] + $value[-1] + $value[-3];
                                }
                            }
                        }
                    }
                }
            }
        }
        return $result;
    }

}

?>
